package com.atguigu.advanced;

import com.atguigu.advanced.pojo.Employee;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class JDBCAdvanced {

    /**
     * 手动进行ORM映射
     */
    @Test
    public void testORM() throws Exception {
        // 1. 注册驱动(JDK6以后可以省略)

        // 2. 获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu?user=root&password=mysqlmima");

        // 3. 预编译SQL语句得到PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT emp_id, emp_name, emp_salary, emp_age FROM t_emp WHERE emp_age > ?");

        // 4. 为占位符赋值, 然后执行SQL语句，接收返回的结果集
        preparedStatement.setInt(1, 2);
        ResultSet resultSet = preparedStatement.executeQuery();

        // 5. 遍历结果集
        List<Employee> employeeList = new ArrayList<>();
        while (resultSet.next()) {
            int emp_id = resultSet.getInt("emp_id");
            String emp_name = resultSet.getString("emp_name");
            double emp_salary = resultSet.getDouble("emp_salary");
            int emp_age = resultSet.getInt("emp_age");
            // 封装对象
            Employee employee = new Employee(emp_id, emp_name, emp_salary, emp_age);
            employeeList.add(employee);
        }
        employeeList.stream().forEach(System.out::println);


        // 6. 释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    /**
     * 获取回显的主键
     */
    @Test
    public void testReturnPK() throws Exception {
        // 1. 注册驱动(JDK6以后可以省略)

        // 2. 获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu?user=root&password=mysqlmima");

        // 3. 预编译SQL语句, 并且设置主键回显, 返回新增数据的注解列的值
        String sql = "INSERT INTO t_emp (emp_name, emp_salary, emp_age) VALUES (?, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);

        // 4. 为占位符赋值, 然后执行SQL语句，接收返回的结果
        Employee employee = new Employee(null, "张三", 8500.00, 38);
        preparedStatement.setString(1, employee.getEmpName());
        preparedStatement.setDouble(2, employee.getEmpSalary());
        preparedStatement.setInt(3, employee.getEmpAge());
        int result = preparedStatement.executeUpdate();

        // 5. 处理结果
        ResultSet generatedKeys = null;
        if (result > 0) {
            System.out.println("插入成功");
            // 获取到主键, 回显到employee对象empId中
            generatedKeys = preparedStatement.getGeneratedKeys();
            if (generatedKeys.next()) {
                employee.setEmpId(generatedKeys.getInt(1));
            }
            System.out.println(employee);
        } else {
            System.out.println("插入失败");
        }


        // 6. 释放资源
        if (generatedKeys != null) {
            generatedKeys.close();
        }
        preparedStatement.close();
        connection.close();
    }

    /**
     * 测试多条记录的插入
     */
    @Test
    public void testMoreInsert() throws Exception {
        // 1. 注册驱动(JDK6以后可以省略)

        // 2. 获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "mysqlmima");

        // 3. 预编译SQL语句
        String sql = "INSERT INTO t_emp (emp_name, emp_salary, emp_age) VALUES (?, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // 4. 循环为占位符赋值, 然后执行SQL语句，接收返回的结果
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setString(1, "张" + i);
            preparedStatement.setDouble(2, 100.0 + i);
            preparedStatement.setInt(3, 20 + i);
            preparedStatement.executeUpdate();
        }

        // 6. 释放资源
        preparedStatement.close();
        connection.close();
        System.out.println("耗时：" + (System.currentTimeMillis() - start));
    }

    /**
     * 测试批量插入数据
     */
    @Test
    public void testBatchInsert() throws Exception {
        // 1. 注册驱动(JDK6以后可以省略)

        // 2. 获取连接对象, 当设置 rewriteBatchedStatements=true 时，JDBC 驱动会将多个批量操作重写为单个 SQL 语句，从而减少与数据库的通信次数，提高性能。
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu?rewriteBatchedStatements=true", "root", "mysqlmima");

        // 3. 预编译SQL语句, 并且设置主键回显, 返回新增数据的注解列的值, 批量插入时, 必须是VALUES, 不能是VALUE, 并且sql的结尾不能加;分号.
        String sql = "INSERT INTO t_emp (emp_name, emp_salary, emp_age) VALUES (?, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // 4. 循环为占位符赋值, 然后执行SQL语句，接收返回的结果
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setString(1, "刘" + i);
            preparedStatement.setDouble(2, 100.0 + i);
            preparedStatement.setInt(3, 20 + i);

            // 执行方式改为addBatch();
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();

        // 5. 释放资源
        preparedStatement.close();
        connection.close();
        System.out.println("耗时：" + (System.currentTimeMillis() - start));
    }
}
